package repeat.personal.page;

import org.apache.commons.collections.MapUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 复杂分页查询
 * @author : Zhanggq
 * @date : 2019/8/14 09:55
 */
@Repository
public class PageRepository {

    private final Logger logger = LoggerFactory.getLogger(PageRepository.class);

    @Autowired
    private EntityManager em;

    private static final String COUNT_STATEMENT_KEYWORDS = "SELECT COUNT(*) FROM ";
    private static final String COUNT_ALIAS_KEYWORDS = " THIS_COUNT_";
    private static final String WHERE_CLAUSE_KEYWORDS = " WHERE ";
    private static final String OR_CLAUSE_KEYWORDS = " OR ";
    private static final String ORDERBY_CLAUSE_KEYWORDS = " ORDER BY ";
    private static final String GROUPBY_CLAUSE_KEYWORDS = " GROUP BY ";

    private static final String QUERY_TYPE_SQL = "sql";

    /**
     * 移除语句中的排序语句
     * @param sql
     * @return
     */
    protected String removeOrderByClause(String sql) {
        int index = sql.toUpperCase().lastIndexOf(ORDERBY_CLAUSE_KEYWORDS);
        if(index != -1) {
            sql = sql.substring(0, index);
        }

        return sql;
    }

    /**
     * 移除语句中的分组语句
     * @param sql
     * @return
     */
    protected String removeGroupByClause(String sql) {
        int index = sql.toUpperCase().lastIndexOf(GROUPBY_CLAUSE_KEYWORDS);
        if(index != -1) {
            sql = sql.substring(0, index);
        }

        return sql;
    }

    /**
     * 根据一个sql语句构造对应的计数语句
     * @param sql
     * @return
     */
    protected String getCountSQLString(String sql) {
        return COUNT_STATEMENT_KEYWORDS + "(" + sql + ")" + COUNT_ALIAS_KEYWORDS;
    }

    /**
     * 通过命名参数形式的sql语句以及命名参数集合，获取一个Query对象。
     * @param sql 命名参数式的sql语句。
     * @param properties 命名参数集合。
     * @return 基于SQL查询的Query对象。
     */
    protected Query getQuery(String sql, Map<String, Object> properties) {
        Query query = em.createNativeQuery(sql);

        if(MapUtils.isNotEmpty(properties)) {
            Iterator<Map.Entry<String, Object>> iterator = properties.entrySet().iterator();
            while(iterator.hasNext()) {
                Map.Entry<String, Object> entry = iterator.next();
                try {
                    Object param = query.getParameter(entry.getKey());
                    if(null != param) {
                        query.setParameter(entry.getKey(), entry.getValue());
                    }
                } catch (Exception ex) {
                    logger.debug("无需设置的属性：{}", entry.getKey());
                }
            }
        }

        return query;
    }

    /**
     * 通过命名参数形式的sql语句以及命名参数集合，获取一个NativeQueryImpl对象。
     * @param sql 命名参数式的sql语句。
     * @param properties 命名参数集合。
     * @return 基于SQL查询的Query对象。
     */
    protected NativeQueryImpl getNativeQueryImpl(String sql, Map<String, Object> properties) {
        Query query = getQuery(sql, properties);
        return query.unwrap(NativeQueryImpl.class);
    }

    /**
     * 通过SQL的Query对象获取对应的Query的计数语句
     * @param sqlString
     * @param namedParameters
     * @return
     */
    protected Query getCountQueryByQuery(String sqlString, Map<String, Object> namedParameters) {
        String queryString = getCountSQLString(sqlString);
        Query countQuery = getQuery(queryString, namedParameters);
        return countQuery;
    }

    /**
     * 用于SQL方式分页查询中的总数计算
     * @param query 查询对象
     * @param namedParameters 参数
     * @param startIndex 查询下标
     * @param count 每页查询数量
     * @return
     */
    protected int getPagingSqlQueryTotalCount(NativeQueryImpl query, Map<String, Object> namedParameters, Integer startIndex, Integer count) {
        String sqlString = query.getQueryString();
        sqlString = removeOrderByClause(sqlString);
//        sqlString = removeGroupByClause(sqlString);

        Query countQuery = getCountQueryByQuery(sqlString, namedParameters);
        Object res = countQuery.getResultList();
        Number total = res == null ? 0 : (Number) ((List) res).get(0);

        if (startIndex >= 0) {
            query.setFirstResult(startIndex * count);
        } else {
            query.setFirstResult(0);
        }

        if (count > 0) {
            query.setMaxResults(count);
        } else {
            query.setMaxResults(Integer.MAX_VALUE);
        }

        return total.intValue();
    }


    /**
     * 根据sql进行分页查询，若不指定返回值类型，将返回Map
     * @param sql sql语句
     * @param pageRequest 请求的分页对象
     * @param clazz 返回值类型。
     * @return 符合条件的对象集合。
     */
    @SuppressWarnings("unchecked")
    public <T> PageResult<T> getEntitiesPagingBySql(String sql, PageRequest pageRequest, Class<T> clazz) {
        // 查询参数
        Map<String, Object> namedParameters = pageRequest.getData();
        // 真实的下标地址，如请求分页第1页，实际下标为0
        Integer startIndex = pageRequest.getPageNum() - 1;
        startIndex = startIndex < 0 ? 0 : startIndex;
        // 每页分页数
        Integer count = pageRequest.getPageSize();
        count = count == null ? 10 : count;

        NativeQueryImpl query = getNativeQueryImpl(sql, namedParameters);
        if(null != clazz) {
            query.addEntity(clazz);
        } else {
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        }

        int totalCount = getPagingSqlQueryTotalCount(query, namedParameters, startIndex, count);
        List<T> list = query.list();
        return new PageResult<T>(pageRequest.getPageNum(), count, totalCount, list);
    }

}
